﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class GuideFeedbackCtrl
    {
        #region 获取导游评价录入数据

        internal static System.Data.DataTable GetGuideFeedbackData(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT * FROM orgt_GuideFeedback WHERE TourGroupID='{0}' ORDER BY GuideFeedbackID", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static System.Data.DataTable GetGuideFeedbackCiceroniIDGroup(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT CiceroniID FROM orgt_GuideFeedback WHERE TourGroupID='{0}'GROUP BY CiceroniID", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static System.Data.DataTable GetGuideFeedbackGroupAverageDataByCiceroniID(string tourgroupId, string ciceroniId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT Sum(GuideScore)*1.0/Count(GuideScore) as AverageGuideScore FROM orgt_GuideFeedback WHERE TourGroupID='{0}' AND CiceroniID={1}", tourgroupId, ciceroniId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 更新导游服务评价数据

        internal static string CreateGuideFeedbackData(GuideFeedbackEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO orgt_GuideFeedback ([TourgroupID], [TourgroupName], [CiceroniID], [CiceroniName], [GuideScore], [CreateByUserName], [CreateDateTime])
                                         VALUES('{0}', '{1}',{2},'{3}',{4},'{5}','{6}');Select SCOPE_IDENTITY();",
                                          entity.TourgroupID,
                                          entity.TourgroupName,
                                          entity.CiceroniID,
                                          entity.CiceroniName,
                                          entity.GuideScore,
                                          entity.CreateByUserName,
                                          entity.CreateDateTime);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();

        }

        internal static int UpdateGuideFeedbackData(GuideFeedbackEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_GuideFeedback SET [TourgroupID]='{0}', [TourgroupName]='{1}', [CiceroniID]={2}, [CiceroniName]='{3}',
                                                                  [GuideScore]={4},[CreateByUserName]='{5}', [CreateDateTime]='{6}'  
                                                                  WHERE [GuideFeedbackID]={7}",
                                                                  entity.TourgroupID,
                                                                  entity.TourgroupName,
                                                                  entity.CiceroniID,
                                                                  entity.CiceroniName,
                                                                  entity.GuideScore,
                                                                  entity.CreateByUserName,
                                                                  entity.CreateDateTime,
                                                                  entity.GuideFeedbackID);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteGuideFeedbackData(string guideFeedbackId)
        {
            if (guideFeedbackId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM orgt_GuideFeedback WHERE GuideFeedbackID={0}", guideFeedbackId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

       
        
        #region 获取导游服务统计数据

        internal static bool JudgeGuideStatisticsExistForTourgroup(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT * FROM orgt_GuideFeedbackStatistics WHERE TourGroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0);
        }

        internal static object GetGuideFeedbackDataStatisticsData(string ciceroniId, string fromDateTime, string toDateTime)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT * FROM orgt_GuideFeedbackStatistics WHERE CiceroniID='{0}' AND CreateDateTime BETWEEN '{1}' AND '{2}'", ciceroniId, fromDateTime, toDateTime);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetGuideStatisticsGroup(string formDateTime, string toDateTime)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT CiceroniID FROM orgt_GuideFeedbackStatistics WHERE CreateDateTime BETWEEN '{0}' AND '{1}' GROUP BY CiceroniID", formDateTime, toDateTime);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static DataTable GetGuideStatisticsGroupAverage(string ciceroniId, string fromDateTime, string toDateTime)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT Sum(GuideScore)/Count(GuideScore) as AverageGuideScore FROM orgt_GuideFeedbackStatistics WHERE CiceroniID={0} AND CreateDateTime BETWEEN '{1}' AND '{2}'", ciceroniId, fromDateTime, toDateTime);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion


        #region 更新导游服务统计数据

        internal static int CreateGuideFeedbackStatisticsData(List<ChinaDynastyTours.Models.GuideFeedbackStatisticsEntity> Entities)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();
            foreach (var entity in Entities)
            {
                sql.AppendFormat(@"INSERT INTO orgt_GuideFeedbackStatistics ([TourgroupID], [TourgroupName], [CiceroniID], [CiceroniName], [GuideScore], [CreateDateTime])
                                         VALUES('{0}', '{1}',{2},'{3}',{4},'{5}');",
                                              entity.TourgroupID,
                                              entity.TourgroupName,
                                              entity.CiceroniID,
                                              entity.CiceroniName,
                                              entity.GuideScore,
                                              entity.CreateDateTime);
            }

            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteGuideFeedbackStatisticsData(string tourgroupId)
        {
            if (tourgroupId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM orgt_GuideFeedbackStatistics WHERE TourgroupID='{0}'", tourgroupId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }       
        
        #endregion
    }
}
